package edu.whu.tao.util;

import java.awt.Color;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;











import jxl.Cell;
import jxl.Range;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
/**
 * 
 * <P>Excel导入导出</P>
 * @author 陶焕(13294175866)
 * @date 2016年3月15日 下午12:41:01
 */
public abstract class ExcelUtil {
    
	private static final Logger log = LoggerFactory.getLogger(ExcelUtil.class);
	
	public static int headFontSize = 11;
	
	public static void exportExcel(OutputStream os, List<String[]> list, List<String> headers,String sheetLable){
        try {
			// 创建工作薄
	        WritableWorkbook workbook = Workbook.createWorkbook(os);
	        // 创建新的一页
	        WritableSheet sheet = workbook.createSheet(sheetLable, 0);
	        //sheet.getSettings().setDefaultRowHeight(440);
	        int row = 0;// 当前行索引
	        // 写入标题
	        for (int i = 0; i < headers.size(); i++) {
	            String item = headers.get(i);
	            WritableFont font = new WritableFont(WritableFont.createFont("黑体"), headFontSize, WritableFont.BOLD);
	            WritableCellFormat format = new WritableCellFormat(font);
	            //format.setWrap(true);//自动换行
	            format.setAlignment(Alignment.CENTRE);
	            format.setVerticalAlignment(VerticalAlignment.CENTRE);
	            //WritableCellFormat format = new WritableCellFormat();
	            //format.setBackground(Colour.GRAY_25);// 设置灰色背景
	            if (i % 2 == 0) {
	            	format.setBackground(getNearestColour("#92D050"));
	            } else {
	                format.setBackground(getNearestColour("#FFC000"));
	            }
	            sheet.addCell(new Label(i, row, item, format));
	            
	            // 设置列宽度(列宽px值/10)
	            sheet.setColumnView(i, 18);
	        }
	        sheet.setRowView(row,440, false);
	        row++;
	        // 写入数据行
	        for (; row < list.size()+1; row++) {
	        	sheet.setRowView(row,440, false);
	            // 获取当前行数据
	            String[] values = list.get(row - 1);
	            for (int i = 0; i < values.length; i++) {
	            	WritableFont font = new WritableFont(WritableFont.createFont("黑体"), headFontSize, WritableFont.NO_BOLD);
		            WritableCellFormat format = new WritableCellFormat(font);
		            //format.setWrap(true);//自动换行
	                sheet.addCell(new Label(i, row, values[i],format));
	            }
	        }
	        
	        // 把创建的内容写入到输出流中，并关闭输出流
	        workbook.write();
	        workbook.close();
        } catch (Exception e) {
			// TODO: handle exception
        	log.error("导出Excel出错:{}", e);
		}
    }
	
	
	public static Colour getNearestColour(String strColor) {
		Color cl = Color.decode(strColor);
		Colour color = null;
		Colour[] colors = Colour.getAllColours();
		if ((colors != null) && (colors.length > 0)) {
			Colour crtColor = null;
			int[] rgb = null;
			int diff = 0;
			int minDiff = 999;
			for (int i = 0; i < colors.length; i++) {
				crtColor = colors[i];
				rgb = new int[3];
				rgb[0] = crtColor.getDefaultRGB().getRed();
				rgb[1] = crtColor.getDefaultRGB().getGreen();
				rgb[2] = crtColor.getDefaultRGB().getBlue();
				diff = Math.abs(rgb[0] - cl.getRed()) + Math.abs(rgb[1] - cl.getGreen())
						+ Math.abs(rgb[2] - cl.getBlue());
				if (diff < minDiff) {
					minDiff = diff;
					color = crtColor;
				}
			}
		}
		if (color == null) {
			color = Colour.BLACK;
		}
		return color;
	}
	
	@Deprecated
    public static List<String[]> importExcel(Workbook workbook,int sheetIndex) {
        List<String[]> list = new ArrayList<String[]>();
        //Workbook workbook = null;
        
        // 从读取流创建只读Workbook对象
        //workbook = Workbook.getWorkbook(is);
        // 获取第sheetIndex张Sheet表
        Sheet readsheet = workbook.getSheet(sheetIndex);
        // 总列数
        int colSum = readsheet.getColumns();
        // 总行数
        int rowSum = readsheet.getRows();
        // 表头中文信息
        String[] headers = new String[colSum];
        int row = 0;
        // 读取表头
        for (int i = 0; i < colSum; i++) {
            Cell cell = readsheet.getCell(i, 0);
            headers[i] = cell.getContents();
            LogUtils.println(headers[i]);
        }
        row++;
        
        // 获取指定单元格的对象引用
        for (; row < rowSum; row++) {
            String[] values = new String[colSum];
            for (int i = 0; i < colSum; i++) {
                Cell cell = readsheet.getCell(i, row);
                // 根据表头CN获取EN
                values[i] = cell.getContents();
                LogUtils.println("sheetIndex:"+sheetIndex+";"+"行:"+row+";"+"列:"+i+";"+"值:"+values[i]+";");
            }
            list.add(values);
        }
        
        return list;
    }
    
    /**
     * jxl只能获取2003版的excel
     * @param is 输入流
     * @return
     */
    @Deprecated
    public static Map<Integer, List<String[]>> importExcelAll(InputStream is){
        Workbook workbook = null;
        Map<Integer, List<String[]>> result = null;
        try {
            // 从读取流创建只读Workbook对象
            workbook = Workbook.getWorkbook(is);
            result = new HashMap<Integer, List<String[]>>();
            int sheetCount = workbook.getNumberOfSheets();
            for(int i = 0;i < sheetCount; i++){
                result.put(i, importExcel(workbook,i));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if(workbook!=null){
                workbook.close();
            }
        }
        return result;
    }

    
    /**
     * 
     * <p>读取excel的一个sheet</p>
     * @param workbook
     * @param sheetIndex
     * @param headerRowCount 头的行数
     * @return
     * @author 陶焕(13294175866)
     */
    public static ExcelReadSheetResult readExcelSheet(Workbook workbook,int sheetIndex,int headerRowCount) {
    	
    	ExcelReadSheetResult result = new ExcelReadSheetResult();
    	result.setHeaderRowCount(headerRowCount);
    	result.setSheetIndex(sheetIndex);
        // 获取第sheetIndex张Sheet表
        Sheet readsheet = workbook.getSheet(sheetIndex);
        String sheetName = readsheet.getName();
        result.setSheetName(sheetName);
        
        //获取合并的单元格
        Range[] rangeCell = readsheet.getMergedCells();
        // 总列数
        int colSum = readsheet.getColumns();
        result.setTotalComulnCount(colSum);
        log.info("总列数:{}", colSum);
        LogUtils.println("总列数:"+colSum);
        // 总行数
        int rowSum = readsheet.getRows();
        result.setTotalRowCount(rowSum);
        LogUtils.println("总行数:"+rowSum);
        log.info("总行数:{}", rowSum);
        result.setHeaderColumnCount(colSum);
        // 表头中文信息
        
        int row = 0;
        int realRow = headerRowCount;
        // 读取表头
        for(int j=0; j < headerRowCount; j++){
        	String[] headers = new String[colSum];
	        for (int i = 0; i < colSum; i++) {
	        	String str = null;  
                str = readsheet.getCell(i, row).getContents();  
                for (Range r : rangeCell) {  
                    if (row >= r.getTopLeft().getRow()  
                            && row <= r.getBottomRight().getRow()  
                            && i >= r.getTopLeft().getColumn()  
                            && i <= r.getBottomRight().getColumn()) {  
                        str = readsheet.getCell(r.getTopLeft().getColumn(),  
                                r.getTopLeft().getRow()).getContents();  
                    }  
                }  
                if(StringUtils.isBlank(str)){
        			headers[i] = "";
                }else {
                	headers[i] = str.trim();
				}
                LogUtils.println("头部:sheetIndex:"+sheetIndex+";"+"行:"+row+";"+"列:"+i+";"+"值:"+headers[i]+";");
	        }
	        row++;
	        result.getHeaderDatas().add(headers);
        }
        
        //处理表头
        List<String[]> headerDatas = result.getHeaderDatas();
        List<String> handledHeader = new ArrayList<String>();
        for(int i = 0; i < colSum; i++ ){
        	String[] tmpStrArr = new String[headerDatas.size()];
        	for (int j = 0; j < headerDatas.size(); j++) {
        		String[] strings = headerDatas.get(j);
        		tmpStrArr[j] = strings[i];
			}
        	String handleStr = StringUtils.join(tmpStrArr, "-");
        	LogUtils.println("处理后的头部:" + handleStr);
        	handledHeader.add(handleStr);
        }
        result.setHandledHeader(handledHeader);
        
        // 获取指定单元格的对象引用
        for (; row < rowSum; row++) {
        	boolean isEmpty = true;//是否是空行
            String[] values = new String[colSum];
            for (int i = 0; i < colSum; i++) {
            	String str = null;  
                str = readsheet.getCell(i, row).getContents();  
                for (Range r : rangeCell) {  
                    if (row >= r.getTopLeft().getRow()  
                            && row <= r.getBottomRight().getRow()  
                            && i >= r.getTopLeft().getColumn()  
                            && i <= r.getBottomRight().getColumn()) {  
                        str = readsheet.getCell(r.getTopLeft().getColumn(),  
                                r.getTopLeft().getRow()).getContents();  
                    }  
                }  
                if(StringUtils.isBlank(str)){
                	values[i] = "";
                }else {
                	values[i] = str.trim();
                	isEmpty = false;
				}
                

                //LogUtils.println("内容:sheetIndex:"+sheetIndex+";"+"行:"+row+";"+"列:"+i+";"+"值:"+values[i]+";");
            }
            if(isEmpty){
            	continue;
            }else {
            	realRow++;
			}
            result.getBodyDatas().add(values);
        }
        result.setTotalRowCount(realRow);
        LogUtils.println("真实总行数:"+realRow);
        log.info("真实总行数:{}", realRow);
        return result;
    }
    
    /**
     * jxl只能获取2003版的excel
     * @param is 输入流
     * @param headerRowCounts 每个sheet的头的行数
     * @return
     */
    public static ExcelReadTotalResult readExcelAll(InputStream is,int... headerRowCounts) throws Exception {
        Workbook workbook = null;
        ExcelReadTotalResult result = null;
        try {
            // 从读取流创建只读Workbook对象
            workbook = Workbook.getWorkbook(is);
            result = new ExcelReadTotalResult();
            int sheetCount = workbook.getNumberOfSheets();
            result.setSheetCount(sheetCount);
            for(int i = 0;i < sheetCount; i++){
            	result.getDatas().add(readExcelSheet(workbook,i,headerRowCounts[i]));
            }
        } catch (Exception e) {
        	log.error("读取excel异常:{}",e);
            e.printStackTrace();
            throw e;
        } finally {
            if(workbook!=null){
                workbook.close();
            }
        }
        return result;
    }
    
    public static void main(String[] args) {
    	try {
			//importExcelAll(new FileInputStream("C:\\Users\\whu\\Desktop\\海运散装采购模板.xls"));
    		//importExcelAll(new FileInputStream("D:\\test.xls"));
    		readExcelAll(new FileInputStream("C:\\Users\\whu\\Desktop\\海运集装箱采购模板.xls"),2);
//    		List<String> headers = new ArrayList<String>();
//    		headers.add("姓名");
//    		headers.add("电话");
//    		List<String[]> list = new ArrayList<String[]>();
//    		String[] str1 = new String[]{"张三","12334"};
//    		String[] str2 = new String[]{"李四减肥快圣诞节疯狂邻居的高考后付款工会经费的合计款规定合法机构和返回该","54656"};
//    		list.add(str1);
//    		list.add(str2);
//    		exportExcel(new FileOutputStream("D:\\test.xls"), list, headers, "联系方式");
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
    
}
